package com.roc.web.action.count;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;

public class ChangeCheckCountSearchCondition  extends BaseSearchCondition{

	 /**
	  * @Fields serialVersionUID : TODO
	  */
	    
	private static final long serialVersionUID = 1L;
	private String minDate;  //开始时间
	private String maxDate;    //结束时间
	
	private String statDate;  //开始时间 --按代理查询
	private String endDate;   //结束时间 -- 按代理查询
	private String agentName; //代理商名称
	private String dealCode; //变更类型
	private String minPasscount; //通过数量
	private String maxPasscount; //通过数量
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();

		// 交易时间
		if (Constance.isNotEmpty(minDate)) {
			filedBeanList.add(new WhereHQLFieldBean("N.TRANS_DT", minDate,
					"=", false));
			filedBeanList.add(new WhereHQLFieldBean("N.TRANS_DT", maxDate,
					"<=", false));
		}
		//代理商
		if(Constance.isNotEmpty(agentName)){//代理商中文(模糊)
			filedBeanList.add(new WhereHQLFieldBean("op.REAL_NAME", agentName.trim(), "like", false,"ALL"));
		}
		
		//变更类型
		 if(Constance.isNotEmpty(dealCode)&&!"99".equals(dealCode)){ //商户信息变更类型
			filedBeanList.add(new WhereHQLFieldBean("N.DEAL_CODE", dealCode.trim(),
					"=", false));
		}
		//通过数量
		 if (Constance.isNotEmpty(minPasscount) && Constance.isNotEmpty(maxPasscount)) {
				filedBeanList.add(new WhereHQLFieldBean("n.passCount",
						minPasscount, ">=", false));
				filedBeanList.add(new WhereHQLFieldBean("n.passCount",
						maxPasscount, "<=", false));
			}
	/*	if (Constance.isNotEmpty(passcount)) {
			filedBeanList.add(new WhereHQLFieldBean("PassCount", passcount.trim(),
					"=", false));
			filedBeanList.add(new WhereHQLFieldBean("PassCount", passcount.trim(),
					">=", false));
			filedBeanList.add(new WhereHQLFieldBean("PassCount", maxDate.trim(),
					"<=", false));
		}*/
		return filedBeanList;
	}
      //统计
	@Override
	protected String getRowCountHQL() {
		StringBuffer sb = new StringBuffer();
		      sb.append(" select count(*) "
		      		+ " FROM ( "
		      		+ " SELECT  SUM( CASE WHEN A.Status='Y' THEN 1 ELSE 0 END ) passCount, "
		      		+ " SUM( CASE WHEN A.status='N' THEN 1 ELSE 0 END ) noPassCount ,A.Agent_id,A.Deal_Code "
		      		+ " FROM t_merinfo_change A  "
		      		+ " WHERE TO_CHAR(A.DT_AUDITOR_TIME, 'yyyyMMdd') >= '" + statDate + "' "
		      		+ " AND  TO_CHAR(A.DT_AUDITOR_TIME, 'yyyyMMdd') <= '" + endDate + "' "
		            + " GROUP BY A.agent_id,A.Deal_Code ) n  "
		            + " LEFT JOIN t_operators op  ON  op.id =n.agent_id "
		            + " LEFT JOIN t_operators o  ON  o.id = op.companyid "
		            + " WHERE  1=1  " ); 
		       /*sb.append(	  " select count(*) "
		       	+  "  FROM ( "
				+ " SELECT T.DEAL_CODE,T.AGENT_ID,COUNT(T.AGENT_ID) TOTAL FROM t_merinfo_change  T "
				+ " WHERE TO_CHAR(T.DT_CREATE_TIME, 'yyyyMMdd') >= '" + statDate + "' "
				+ "  AND  TO_CHAR(T.DT_CREATE_TIME, 'yyyyMMdd') <= '" + endDate + "'  "
				+ " GROUP BY T.AGENT_ID,T.DEAL_CODE ) m  "
				+ " LEFT JOIN ( "
				+ " SELECT  SUM( CASE WHEN A.Status='Y' THEN 1 ELSE 0 END ) passCount, "
				+ "         SUM( CASE WHEN A.status='N' THEN 1 ELSE 0 END ) noPassCount ,A.Agent_id,A.Deal_Code "
				+ "  FROM t_merinfo_change A  "
				+ " WHERE TO_CHAR(A.DT_AUDITOR_TIME, 'yyyyMMdd') >= '" + statDate + "' "
				+ "  AND  TO_CHAR(A.DT_AUDITOR_TIME, 'yyyyMMdd') <= '" + endDate + "' "
				+ " GROUP BY A.agent_id,A.Deal_Code ) n  "
				+ " ON m.agent_id =n.agent_id "
				+ " LEFT JOIN t_operators  op  ON  m.agent_id=op.id "
				+ " WHERE   m.deal_code=n.deal_code  " );*/
		return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
		StringBuffer sb = new StringBuffer();
		 sb.append(" select n.agent_id ,op.real_name, n.deal_code,  n.passCount,n.noPassCount,o.real_name companyName "
		      		+ " FROM ( "
		      		+ " SELECT  SUM( CASE WHEN A.Status='Y' THEN 1 ELSE 0 END ) passCount, "
		      		+ " SUM( CASE WHEN A.status='N' THEN 1 ELSE 0 END ) noPassCount ,A.Agent_id,A.Deal_Code "
		      		+ " FROM t_merinfo_change A  "
		      		+ " WHERE TO_CHAR(A.DT_AUDITOR_TIME, 'yyyyMMdd') >= '" + statDate + "' "
		      		+ " AND  TO_CHAR(A.DT_AUDITOR_TIME, 'yyyyMMdd') <= '" + endDate + "' "
		            + " GROUP BY A.agent_id,A.Deal_Code ) n  "
		            + " LEFT JOIN t_operators op  ON  op.id =n.agent_id "
		            + " LEFT JOIN t_operators o  ON  o.id = op.companyid "
		            + " WHERE 1=1 " ); 
		/*sb.append(  " SELECT  m.agent_id ,op.real_name, n.deal_code, m.total, passCount,noPassCount "
				+ "  FROM ( "
				+ " SELECT T.DEAL_CODE,T.AGENT_ID,COUNT(T.AGENT_ID) TOTAL FROM t_merinfo_change  T "
				+ " WHERE  TO_CHAR(T.DT_CREATE_TIME, 'yyyyMMdd') >= '" + statDate + "' "
				+ "  AND   TO_CHAR(T.DT_CREATE_TIME, 'yyyyMMdd') <= '" + endDate + "' "
				+ " GROUP BY T.AGENT_ID,T.DEAL_CODE ) m  "
				+ " LEFT JOIN ( "
				+ " SELECT  SUM( CASE WHEN A.Status='Y' THEN 1 ELSE 0 END ) passCount, "
				+ "         SUM( CASE WHEN A.status='N' THEN 1 ELSE 0 END ) noPassCount ,A.Agent_id,A.Deal_Code "
				+ "  FROM t_merinfo_change A  "
				+ " WHERE TO_CHAR(A.DT_AUDITOR_TIME, 'yyyyMMdd') >= '" + statDate + "' "
				+ "  AND  TO_CHAR(A.DT_AUDITOR_TIME, 'yyyyMMdd') <= '" + endDate + "' "
				+ " GROUP BY A.agent_id,A.Deal_Code ) n  "
				+ " ON m.agent_id =n.agent_id "
				+ " LEFT JOIN t_operators  op  ON  m.agent_id=op.id "
				+ " WHERE   m.deal_code=n.deal_code  " );*/
		       
		return sb.toString();
	}


	@Override
	protected String getOrderPartOfHQL() {
		// TODO Auto-generated method stub
		return " ORDER BY n.agent_id,n.deal_code ";
	}

	public String getMinDate() {
		return minDate;
	}

	public void setMinDate(String minDate) {
		this.minDate = minDate;
	}

	public String getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(String maxDate) {
		this.maxDate = maxDate;
	}
	public String getStatDate() {
		return statDate;
	}
	public void setStatDate(String statDate) {
		this.statDate = statDate;
	}
	public String getEndDate() {
		return endDate;
	}
	public void setEndDate(String endDate) {
		this.endDate = endDate;
	}
	public String getAgentName() {
		return agentName;
	}
	public void setAgentName(String agentName) {
		this.agentName = agentName;
	}
	public String getDealCode() {
		return dealCode;
	}
	public void setDealCode(String dealCode) {
		this.dealCode = dealCode;
	}
	public String getMinPasscount() {
		return minPasscount;
	}
	public void setMinPasscount(String minPasscount) {
		this.minPasscount = minPasscount;
	}
	public String getMaxPasscount() {
		return maxPasscount;
	}
	public void setMaxPasscount(String maxPasscount) {
		this.maxPasscount = maxPasscount;
	}


	

	
	
	
}
